import MySQLdb, numpy, csv, pdb

'''

Sample without replacement from the database of sample ids.  This is meant for genotype error-testing
Generally, we are taking a sample of 20%.  We are over-sampling, randomly so that any empty sample can be
skipped (if needed) and we can pick another random sample.

Steps for the data:
1.)  re-sort by plate and well-number
2.)  remove Negative Control entries
3.)  add an id column that is of int type and auto-incremented
4.)  pick a number of random integers (from uniform, discrete), equal to 20% sample size
5.)  add these to a list, dropping repeats
6.)  select records from dbase with these integers as their auto-id value
7.)  write these out to a csv file

In this code, ttrs_sample_sheets is the table containing all individual ids.

'''


database = MySQLdb.connect("params")
cursor = database.cursor()


class excelSingleSpace:
    """class for csv module to work correctly"""
    delimiter = ','
    quotechar = '"'
    escapechar = None
    doublequote = True
    skipinitialspace = False
    lineterminator = '\r'

def sampleCount(percentage):
    # get the count of non-negative control samples
    query = ("SELECT COUNT(*) FROM ttrs_sample_sheets;")
    cursor.execute(query)
    count = cursor.fetchall()[0][0]
    sample = int(round(count*percentage,0))
    return count,sample


def randomNum(n,percent):
    rand = []
    # select random integers from a uniform distribution
    # between start and stop positions
    while len(rand) < percent:
        r = numpy.random.randint(0,n)
        if r not in rand:
            rand.append(r)
    return rand
    
def selectSamples(randList):
    allRecords = ()
    randList.sort()
    #pdb.set_trace()
    for r in randList:
        query = (("SELECT * FROM ttrs_sample_sheets WHERE id = %i") % (r))
        cursor.execute(query)
        record = cursor.fetchall()[0]
        allRecords += (record,)
    return allRecords
    
def write(records):
    handle = open('randomSelections.csv','w')
    w = csv.writer(handle, dialect = excelSingleSpace)
    for element in records:
        w.writerow(element)
    handle.close()
    
#pdb.set_trace()
percentage = 0.20
n,percent = sampleCount(percentage)
randList = randomNum(n,percent)
records = selectSamples(randList)
write(records)

